import requests
from lxml import etree
import json
import pymysql

class Jc(object):
    def __init__(self):
        base_url = 'http://www.jc.net.cn/market/search.html?keys=&area_name=&province=0&city=0&t1=%E9%BB%91%E8%89%B2%E5%8F%8A%E6%9C%89%E8%89%B2%E9%87%91%E5%B1%9E&t2=%E9%92%A2%E4%B8%9D%E7%BB%B3&st=&jgjs=&pno='
        self.headers = {
            'User-Agent':'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/60.0.3112.101 Safari/537.36'
        }
        self.url_list = [base_url + str(i) for i in range(1,10)]
        self.tupe_str = ''

    def get_data(self,url):
        response = requests.get(url=url,headers=self.headers)
        return response.content.decode()

    def parse(self,response):
        html = etree.HTML(response)
        node_list = html.xpath('//tr[@bgcolor="#FFFFFF"]')
        tupe_str = 'insert into product values'
        for node in node_list:
            a = node.xpath('./td[1]/a/text()')[0]
            b = node.xpath('./td[2]/text()')[0].strip()
            c = node.xpath('./td[3]/text()')[0].strip()
            d = node.xpath('./td[8]/text()')[0]
            e = node.xpath('./td[9]/a/text()')[0]
            f = node.xpath('./td[10]/text()')[0]
            temp = (0,a,b,c,d,e,f)
            tupe_str += str(temp) + ','
        self.tupe_str = tupe_str.rstrip(',')

    def save(self,tupe_str):
        with open('tupe_str.json','a') as f:
                f.write(tupe_str)

    def write_mysql(self):
        conn = pymysql.Connect(host='localhost',user='root',port=3306,
                               password='mysql',charset='utf8',database='python')
        cs = conn.cursor()
        cs.execute(self.tupe_str)
        cs.close()
        conn.commit()

    def run(self):
        for url in self.url_list:
            # print(url)
            response = self.get_data(url)
            self.parse(response)
            self.write_mysql()

if __name__ == '__main__':
    jc = Jc()
    jc.run()

